package com.naiterui.ehp.bs.patient.repository;

import java.util.Collection;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.naiterui.common.repository.core.BaseJpaRepository;
import com.naiterui.ehp.bp.domain.Doctor;
import com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO;

/**
 * <pre>
 * 医生信息持久化实现
 * </pre>
 *
 * @author fangguanhong
 * @date 2020/4/9 下午6:03
 * @since 1.0.0
 */
@Repository
public interface IApDoctorRepository extends BaseJpaRepository<Doctor, Long> {

    @Query(nativeQuery = true, value = "select d.id,d.name,d.status,d.head_url,d.department_id,d.custom_department,d.title_id,d.account_status from" +
            " doctor d,patient_personal_doctor p where p.relation_flag =0 and d.id=p.doctor_id and p.patient_id= ?1 and  d.account_status= ?2 limit" +
            " ?3,?4")
    List<Object[]> findPatientPersonalList(Long patientId, Integer accountStatus, Integer start, Integer num);

    @Query(nativeQuery = true, value = "select d.id,d.name,d.status,d.head_url,d.department_id,d.custom_department,d.title_id,d.account_status from" +
            " doctor d,patient_personal_doctor p where p.relation_flag =0 and d.id=p.doctor_id and p.patient_id= ?1 and  d.account_status= ?2")
    List<Object[]> findPatientPersonalList(Long patientId, Integer accountStatus);

    @Query(nativeQuery = true, value = "select count(1) from doctor d,patient_personal_doctor p where p.relation_flag = 0 and d.id=p.doctor_id and " +
            " p.patient_id= ?1 and d.account_status= ?2")
    Long countPrivateDoctorNum(Long patientId, Integer accountStatus);

    /**
     * 获取医生列表
     *
     * @param patientId
     * @param pageable
     *
     * @return
     */
    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
            " from Doctor d, PatientPersonDoctor r where r.relationFlag = 0 and r.personPK.patientId = ?1 and d" +
            ".id=r.personPK.doctorId and d.accountStatus = 0 order by r.createAt desc")
    Page<DoctorVO> findByPatientIdPage(Long patientId, Pageable pageable);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
            " from Doctor d where d.accountStatus = 0 and d.type = 0 order by d.createdAt asc")
    Page<DoctorVO> findAllPage(Pageable pageable);

    @Query(nativeQuery = true, value = "select count(patient_id) from attend where doctor_id = ?1")
    Integer countAttendNum(Long doctorId);

    @Query(nativeQuery = true, value = "select count(patient_id) from patient_personal_doctor where doctor_id = ?1")
    Integer countPersonalNum(Long doctorId);

    @Query(nativeQuery = true, value = "select h.name from dr_hospital h where h.id=?1")
    List<String> getHospitalName(Integer id);

    @Query(nativeQuery = true, value = "select d.* from doctor d where d.id in (select p.doctor_id from patient_personal_doctor p where p" +
            ".patient_id=?1) and d.account_status=?2")
    List<Doctor> getPersonalDoctor(Long patientId, Integer accountStatus);

    @Query(nativeQuery = true, value = "select d.* from doctor d where d.id in (select p.doctor_id from patient_personal_doctor p where p" +
            ".patient_id=?1)")
    List<Doctor> getPersonalDoctor(Long patientId);

    List<Doctor> findByIdIn(Collection<Long> id);

    @Query(nativeQuery = true, value = "select count(1) from patient_personal_doctor where doctor_id=?1")
    Integer countPersonalPatients(Long doctorId);

    @Query(nativeQuery = true, value = "select count(1) from dr_consult_comment where doctor_id=?1")
    Integer countConsultComment(Long doctorId);

    @Query(nativeQuery = true, value = "select count(1) from dr_consult_comment where doctor_id=?1 and (star_level=4 or star_level=5)")
    Integer countConsultCommentLevel(Long doctorId);

    List<Doctor> findByIdInAndAccountStatus(Collection<Long> doctorIds, Integer accountStatus);

    @Query(nativeQuery = true, value = "select doctor_id,count(1) from patient_personal_doctor where doctor_id in (?1)  group by doctor_id")
    List<Object[]> getDoctorPatientNumByIds(String queryStr);

    @Query(nativeQuery = true, value = "select doctor_id,count(1) from dr_consult_comment where doctor_id in (?1) group by doctor_id")
    List<Object[]> getDoctorCommentNumByIds(String queryStr);

    @Query(nativeQuery = true, value = "select doctor_id, count(1) from dr_consult_comment where doctor_id in(?1) and (star_level=4  or " +
            "star_level=5) group by doctor_id")
    List<Object[]> getDoctorCommentNumByIdsLevel(String queryStr);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
            "from Doctor d, PatientPersonDoctor r where r.relationFlag = 0 and r.personPK.patientId = ?1 and d.id=r.personPK" +
            ".doctorId and d.accountStatus = ?2 order by r.createAt asc")
    List<DoctorVO> getMyDoctorList(Long patientId, Integer accountStatus);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
        "from Doctor d, UserLabel u where d.id = u.userId and u.userType = 1 and u.labelId = ?3 and d.departmentId in (?1) and d.cityId in (?2) ")
    Page<DoctorVO> getClickDoctorByDepartmentAndCity(Collection<Integer> departmentIds, Collection<Integer> cityIds,
        Long labelId, Pageable pageable);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
            "from Doctor d  where  d.departmentId in (?1) and d.cityId in (?2) ")
    Page<DoctorVO> getClickDoctorByDepartmentAndCityAll(Collection<Integer> departmentIds, Collection<Integer> cityIds, Pageable pageable);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
        "from Doctor d, UserLabel u where d.id = u.userId and u.userType = 1 and u.labelId = ?2 and d.departmentId in (?1) ")
    Page<DoctorVO> getClickDoctorByDepartment(Collection<Integer> departmentIds, Long labelId, Pageable pageable);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
            "from Doctor d  where  d.departmentId in (?1) ")
    Page<DoctorVO> getClickDoctorByDepartmentAll(Collection<Integer> departmentIds, Pageable pageable);


    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
        "from Doctor d, UserLabel u where d.id = u.userId and u.userType = 1 and u.labelId = ?2 and d.cityId in (?1) ")
    Page<DoctorVO> getClickDoctorByCity(Collection<Integer> cityIds, Long labelId, Pageable pageable);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
            "from Doctor d  where  d.cityId in (?1) ")
    Page<DoctorVO> getClickDoctorByCityAll(Collection<Integer> cityIds,Pageable pageable);


    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
        "from Doctor d, UserLabel u where d.id = u.userId and u.userType = 1 and u.labelId = ?1 ")
    Page<DoctorVO> getClickDoctor(Long labelId, Pageable pageable);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
            "from Doctor d  ")
    Page<DoctorVO> getClickDoctorAll(Pageable pageable);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
        " as departmentId, d.customDepartment as department, d.titleId as " +
        "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
        "from Doctor d, UserLabel u where d.id = u.userId and  u.userType = 1 and u.labelId = ?3 and (d.name like ?1 or d.hospitalId in (?2))")
    Page<DoctorVO> getClickDoctorByDoctorNameOrHospital(String name, List<Integer> hospitalIds,Long labelId, Pageable pageable);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
            "from Doctor d where (d.name like ?1 or d.hospitalId in (?2))")
    Page<DoctorVO> getClickDoctorByDoctorNameOrHospitalAll(String name, List<Integer> hospitalIds, Pageable pageable);


    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
        " as departmentId, d.customDepartment as department, d.titleId as " +
        "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
        "from Doctor d, UserLabel u where d.id = u.userId and u.userType = 1 and u.labelId = ?2 and d.name like ?1")
    Page<DoctorVO> getClickDoctorByDoctorName(String name,Long labelId, Pageable pageable);

    @Query(value = "select new com.naiterui.ehp.bs.patient.vo.doctor.DoctorVO( d.id as doctorId, d.name as name, d.headUrl as photo, d.departmentId" +
            " as departmentId, d.customDepartment as department, d.titleId as " +
            "titleId, d.hospitalName as hospital, d.hospitalId as hospitalId, d.expertise as expertise, d.status as authStatus) " +
            "from Doctor d  where  d.name like ?1")
    Page<DoctorVO> getClickDoctorByDoctorNameAll(String name, Pageable pageable);
}
